package com.ants.utils;

import com.ants.model.ColumnModel;
import com.ants.model.TableModel;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 蚂蚁会花呗
 * @date 2021/11/2 14:23
 */
public class JDBCUtils {

    private static Connection conn = null;

    static final String DRIVER = "com.mysql.cj.jdbc.Driver";

    public JDBCUtils() {

    }

    public JDBCUtils(String url,String username,String password) {
        /** 定义数据库连接对象 */
        Connection conn = null;
        try{
            Class.forName(DRIVER);
            // 获取数据库连接对象
            conn = DriverManager.getConnection(url, username, password);
        }catch (ClassNotFoundException | SQLException e){

        }

    }


    /**
     * 判断连接状态
     * @param url
     * @param username
     * @param password
     * @return
     */
    public static boolean connectionStatus(String url,String username,String password) {
        Connection connection = getConnection(url, username, password);
        if (connection != null){
            try{
                connection.close();
                conn = null;
                return true;
            }catch (SQLException e){
                conn = null;
                return false;
            }
        }
        conn = null;
        return false;
    }



    /**
     * 获取连接
     * @param url
     * @param username
     * @param password
     * @return
     */
    public static Connection getConnection(String url,String username,String password){
        conn = null;
        try{
            Class.forName(DRIVER);
            // 获取数据库连接对象
            conn = DriverManager.getConnection(url, username, password);
            if (conn != null){
                return conn;
            }
            return null;
        }catch (ClassNotFoundException | SQLException e){
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 获取数据
     * @param url
     * @param username
     * @param password
     * @throws SQLException
     */
    public static List<TableModel> getResult(String url,String username,String password){
        conn = null;
        String database = getDatabase(url, username, password);
        //定义数据库连接对象
        if (conn == null){
            conn = getConnection(url, username, password);
        }
        try{

            List<TableModel> list = getTable(url, username, password,database);
            String sql = "select * from `information_schema`.`COLUMNS` where TABLE_SCHEMA = " + "'" + database + "'";
            Statement stmt = null;
            ResultSet rs = null;
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            List<ColumnModel> listColumn = new ArrayList<>();
            while (rs.next()){
                ColumnModel columnModel = new ColumnModel();
                columnModel.setTableName(rs.getString("TABLE_NAME"));
                columnModel.setColumnName(rs.getString("COLUMN_NAME"));
                columnModel.setColumnType(rs.getString("COLUMN_TYPE"));
                columnModel.setColumnComment(rs.getString("COLUMN_COMMENT"));
                columnModel.setColumnKey(rs.getString("COLUMN_KEY").equals("PRI") ? "是" : "否");
                columnModel.setColumnAuto(rs.getString("EXTRA").equals("auto_increment") ? "是" : "否");
                listColumn.add(columnModel);
            }
            if (!list.isEmpty()){
                for (TableModel table : list){
                    for (ColumnModel column : listColumn){
                        if (table.getTableName().equals(column.getTableName())){
                            table.getListColumn().add(column);
                        }
                    }
                }
            }
            conn.close();
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public static List<TableModel> getTable(String url,String username,String password,String database){
        //定义数据库连接对象
        if (conn == null){
            conn = getConnection(url, username, password);
        }
        String sql = "select * from `information_schema`.`TABLES` where TABLE_SCHEMA = " + "'" + database + "'";
        Statement stmt = null;
        ResultSet rs = null;
        List<TableModel> list = new ArrayList<>();
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()){
                TableModel tableModel = new TableModel();
                tableModel.setTableName(rs.getString("TABLE_NAME"));
                tableModel.setTableDesc(rs.getString("TABLE_COMMENT"));
                list.add(tableModel);
            }
            //关闭结果集
            rs.close();
            //关闭表连接对象
            stmt.close();
            return list;
        }catch (SQLException e){
            e.printStackTrace();
            return null;
        }
    }


    /**
     * 获取当前连接的数据库
     * @return
     */
    public static String getDatabase(String url,String username,String password){
        //定义数据库连接对象
        if (conn == null){
            conn = getConnection(url, username, password);
        }
        String strSql = "SELECT database();";
        Statement stmt = null;
        ResultSet rs = null;
        String database = "";
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(strSql);
            while (rs.next()){
                database = rs.getString(1);
            }
            //关闭结果集
            rs.close();
            //关闭表连接对象
            stmt.close();
            return database;
        }catch (SQLException e){
            e.printStackTrace();
            return database;
        }
    }

    public static void main(String[] args) throws SQLException {
        connectionStatus("jdbc:mysql://localhost:3306/eureka_order?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC","root","root");
        getResult("jdbc:mysql://localhost:3306/eureka_order?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC","root","root");
//        if (connectionStatus("jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC","root","root")){
//            String str = getDatabase("jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC","root","root");
//            System.out.println(StringUtils.isBlank(str));
//        }
    }



}
